-- @owner: zou_jialiang050
-- @date: 2024/4/2
-- @testpoint: 在loop外使用pip row返回

--step1:创建嵌套表类型，其中嵌套嵌套表类型;expect:成功
create or replace type tb_type_0012 as table of integer;

--step2:在loop外使用pip row返回调用loop变量;expect:失败
create or replace function func_pipelined_0012(count in number)
returns tb_type_0012 pipelined
as $$
declare
i integer;
begin
for i in 1 .. count loop
    PIPE ROW(i);
end loop;
    pipe row(i + 1);
return;
    pipe row(i + 2);
end;
$$ language plpgsql;
/
select func_pipelined_0012(3);

--step3:在loop外使用pip row;expect:成功
create or replace function func_pipelined_0012(count in number)
returns tb_type_0012 pipelined
as $$
declare
j integer;
begin
j := 200;
for i in 1 .. count loop
    PIPE ROW(i);
end loop;
    pipe row(j + 1);
return;
    pipe row(j + 2);
end;
$$ language plpgsql;
/
select * from table(func_pipelined_0012(1));

--step4:清理环境;expect:成功
drop function if exists func_pipelined_0012;
drop type if exists tb_type_0012 cascade;
drop type if exists type_0012 cascade;
